<?php
#
#
# Copyright TEDECO, 2009. http://tedeco.fi.upm.es
#
# This file is part of kumenya.
#
# Kumenya is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Kumenya is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Foobar.  If not, see <http://www.gnu.org/licenses/>.
#
#
#
?>
<?php
// Editable variables.
$Host = "localhost";
$User = "kumenyaapp";
$Password = "EfJ7i49c3v11";
$DBName = "kumenya_matriculacion";

$subject = $_POST["subject"];
$year = $_POST["year"];
$convocatory = $_POST["convocatory"];

$select_marks = "SELECT personas.CodigoPersona, personas.Nombre, personas.Apellidos, matriculasasignaturas.".$convocatory.", matriculasasignaturas.NotaFinal FROM ((((personas RIGHT JOIN alumnos ON personas.Uid = alumnos.Uid) RIGHT JOIN matriculas ON alumnos.Uid = matriculas.UidAlumno) RIGHT JOIN matriculasasignaturas ON matriculasasignaturas.UidMatricula = matriculas.Uid) RIGHT JOIN asignaturas ON asignaturas.Uid = matriculasasignaturas.UidAsignatura) WHERE (asignaturas.Uid = ".$subject." AND matriculas.Anio = ".$year.") ORDER BY personas.CodigoPersona ASC";
// End editable variables.

function Print_table($res)
{
   $rows = mysql_num_rows($res);

   if (!$rows) printf("<div align=\"center\"><H2>Table is empty.</H2></div>");
   else
   {
      printf("<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1>\n");
      printf("<tr>");
      printf("<th>index</th>");
      $numberfields = mysql_num_fields($res);
      for ($i=0; $i<$numberfields; $i++)// Print head.
      {
          printf("<th>%s</th>", mysql_field_name($res, $i));
      }
      printf("</tr>\n");
      
      $j = 1;
      while ($row = mysql_fetch_row($res))
      {
         printf("<tr>");
         printf("<th>%d</th>", $j++);
         for ($i=0; $i<$numberfields; $i++)
         {
            if ($row[$i] != "") printf("<td>%s</td>", $row[$i]);
            else printf("<td>&nbsp;</td>");
         }
         printf("</tr>\n");
      }
      printf("</TABLE>");
   }
}

// Connect to DB
$link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
mysql_select_db($DBName) or die('Could not select database');

include("../commons/clases.php");
include("../commons/www.php");
include(Accion . "marks_list_aux.php");
require_once 'Excel/reader.php';

$file_name =  $_FILES['file']['name'];
$file_size = $_FILES['file']['size'];
$file_error = $_FILES['file']['error'];
$file_temp = $_FILES['file']['tmp_name'];
//$file_new_name = "list.xls";

// Start Interface
Interfaz::Cabecera(_("Student") . " " . _(" ") . " " . _("List"));

Interfaz::AbrirCuerpo(FALSE);

Interfaz::ProximaPantalla ();

Interfaz::Botones ();
Interfaz::Formulario (Pagina. "import_marks.php","back");
Interfaz::BotonDeEnvio(_("Back"));

Interfaz::CerrarBotones ();
Interfaz::CerrarFormulario ();

$pattern = "/^[0-9]{4}$/";
if (!preg_match($pattern, $year)) echo '<br />Year not valid.<br /><br />';
elseif ($_FILES["file"]["error"] > 0) echo "Upload failed. Try again.";
elseif (strpos($file_name, ".xlsx") || ($file_size > 2*1024*1024))
{
    echo "File type is not .xls or file size bigger than 2Mb.";
}
elseif (strpos($file_name, ".xls"))
{
    // Read excel
    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding('CP1251');
    $data->read($file_temp);

    // Number of rows of page 0
    $num_rows = $data->sheets[0]['numRows'];
    
    // Number of columns of page 0
    $num_columns = $data->sheets[0]['numCols'];

    $result = mysql_query($select_marks);
    $num_students = mysql_num_rows($result);
    for ($i=1; $i<=$num_rows; $i++)
    {
        // [row][column] 
        $student = $data->sheets[0]['cells'][$i][1];
        $mark = $data->sheets[0]['cells'][$i][2];
        $select_id = "SELECT matriculasasignaturas.UidMatricula FROM ((((personas RIGHT JOIN alumnos ON personas.Uid = alumnos.Uid) RIGHT JOIN matriculas ON alumnos.Uid = matriculas.UidAlumno) RIGHT JOIN matriculasasignaturas ON matriculasasignaturas.UidMatricula = matriculas.Uid) RIGHT JOIN asignaturas ON asignaturas.Uid = matriculasasignaturas.UidAsignatura) WHERE (asignaturas.Uid = ".$subject." AND matriculas.Anio = ".$year." AND personas.CodigoPersona = '".$student."')";
        $id = mysql_query($select_id);
        $id = mysql_fetch_row($id);
        $id = $id[0];
        $update_marks = "UPDATE matriculasasignaturas SET ".$convocatory." = ".$mark.", NotaFinal = ".$mark." WHERE UidMatricula = ".$id." AND UidAsignatura = ".$subject;
        $res = mysql_query($update_marks);
        if (!$res)
        {
            $errors = $errors.$student.", ";   
        }
    }
    if ($errors != "")
    {
        echo 'It has happened an error with this students : <br/>'.$errors.'<br/><br/>';
    }
    echo 'Warning: Do not match the number of students in the excel file ('.$num_rows.') with the number of students of the subject ('.$num_students.').<br/><br/>';
    $result = mysql_query($select_marks);
    print_table ($result);
}
else echo "File type is not .xls";

Interfaz::Fin();

exit;
?>
